package com.spring.app.common.dao;

import java.security.MessageDigest;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.catalina.util.MD5Encoder;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.security.authentication.encoding.Md5PasswordEncoder;
import org.springframework.security.core.userdetails.jdbc.JdbcDaoImpl;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.spring.app.common.domain.DBUser;
import com.spring.app.common.domain.UserRegistration;


@Transactional(readOnly=false)
public class UserDAO extends JdbcDaoImpl{
 
public Md5PasswordEncoder pwdencode;


public Md5PasswordEncoder getPwdencode() {
	return pwdencode;
}


public void setPwdencode(Md5PasswordEncoder pwdencode) {
	this.pwdencode = pwdencode;
}


public DBUser select(String username) throws Exception {
   String sql = "SELECT username, password, access FROM users WHERE username = ?";
   Object[] parameters = new Object[] { username };
   RowMapper<Object> set = new RowMapper<Object>() {
      public Object mapRow(ResultSet set, int row) throws SQLException {
    	 DBUser user = new DBUser();
    	 user.setUsername(set.getString("username"));
    	 user.setAccess(set.getInt("access"));
    	 user.setPassword(set.getString("password"));
         return user;
      }
   };
   DBUser user = (DBUser)getJdbcTemplate().queryForObject(sql, parameters, set);
   return user;
}

// to-do .. add preauthroze annotation later here ..
public UserRegistration loadUserProfile(String username) throws Exception{
	 //  String sql = "SELECT * FROM users where username=? RIGHT JOIN user_info ON users.username = user_info.username LEFT JOIN group_users ON users.username = group_users.username;";
		String sql="SELECT u.username, u.group_name FROM user_info as u LEFT JOIN group_users ON u.username = group_users.username RIGHT JOIN users as usr ON u.username = usr.username where u.username=?";
	//String sql = "SELECT * FROM users where username=?";
		Object[] parameters = new Object[] { username };
		RowMapper<Object> set = new RowMapper<Object>() {

		  public Object mapRow(ResultSet set, int row) throws SQLException {
			  UserRegistration user = new UserRegistration();
			  user.setUsername(set.getString("username"));
			  user.setGroup_name(set.getString("group_name"));
		     return user;
		  }
		};
		UserRegistration user = (UserRegistration)getJdbcTemplate().queryForObject(sql, parameters, set);
	//	UserRegistration user = (UserRegistration)getJdbcTemplate().queryForObject(sql, parameters);
		return user;
}

public void add(String username,String password, String group_name) throws Exception {
	  logger.debug("Adding new user");
	  
	  String accessLevel="2";
	  if(group_name.equals("group_premium")){
		  accessLevel="1";
	  }
	  
	  try{
		// update users table
	//	String sql = "insert into users(username, password, access) values (:username, :password, :access)";
		  String sql = "insert into users(username, password, access) values (?,?,?)";  
		//Object salt = null;
		//password = pwdencode.encodePassword(password, salt);
        MessageDigest md = MessageDigest.getInstance("MD5");
        md.update(password.getBytes());
        byte byteData[] = md.digest();
        //convert the byte to hex format method 1
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < byteData.length; i++) {
         sb.append(Integer.toString((byteData[i] & 0xff) + 0x100, 16).substring(1));
        }
     //   System.out.print("credentials passed"+""+username+""+sb.toString()+""+accessLevel);
	//	Map<String, Object> parameters = new HashMap<String, Object>();
	//	parameters.put("username", username);
	//	parameters.put("password", sb.toString());
	//	parameters.put("access", accessLevel);
		getJdbcTemplate().update(sql, new Object[] {username,sb.toString(),accessLevel});
		
		
		
		
		
		// update group_users table
	//	String group_users_sql = "insert into group_users(group_name, username) values (:group_name, :username)";
		String group_users_sql = "insert into group_users(group_name, username) values (?,?)";
	//	Map<String, Object> parameters_grouping = new HashMap<String, Object>();
	//	parameters_grouping.put("group_name", group_name);
	//	parameters_grouping.put("username", username);
		getJdbcTemplate().update(group_users_sql, new Object[] {group_name,username});
		
		// update user_info table
//		String user_info_sql = "insert into user_info(username, group_name, interested_in, martial_status, profession, education, intention, do_you_have_kids, interest, about_me) values " +
//		    "(:username, :group_name, :interested_in, :martial_status, :profession, :education, :intention, :do_you_have_kids, :interest, :about_me)";
		String user_info_sql = "insert into user_info(username, group_name) values (?,?)";
		getJdbcTemplate().update(user_info_sql, new Object[] {username,group_name});
		
		
	
		
		
	  }catch(Exception ex){
		  System.out.print(ex.getMessage());
	  }
}
}